Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Enhancements

Some additional enhancements may help improve the performance of your batch processing system. A few of the items that can help are listed here; the list also includes some enhancements that will not help performance. Each item is accompanied by an explanation.

  Block size. In a batch processing system, you may find that increasing the database block size can improve performance and reduce the amount of wasted space in the database. In a typical batch processing environment, many of the queries access the database in a sequential manner; a larger block brings more of these rows into the SGA at once. Having these additional rows in the SGA increases performance because you will use the rows; thus, access to them results in a cache hit.
  Clusters. Depending on the application, you may benefit from clusters. The benefit you receive depends on your data access patterns and your application.
  Hash clusters. Because batch processing typically does not use equality queries, a hash cluster will most likely degrade performance. Hash clusters are useful when your queries involve equality statements on the hash key.
  Indexes. Usually, batch processing systems depend heavily on the use of indexes. How and when you use indexes depends on your application. If you do use indexes, you may want to take advantage of the FULL hint to bypass the index when you know you will be doing a table scan.
  Direct write sorts. Direct write sorts is a new feature in Oracle that allows you to bypass the SGA when performing sort operations. Doing so prevents sorts from taking unnecessary space in the SGA; the result is more SGA space for other operations.
  Parallel Query option. The Oracle Parallel Query option will almost certainly improve the performance of your batch processing system. Parallel queries can be performed on sorts, joins, and table scans and improve the performance of those operations.

Many of these enhancements can help performance in a batch system. I recommend the use of the Oracle Parallel Query option, if at all possible. The specific effect on your system will vary, but most of the hints in this chapter work well for most situations.

Parallel Query Option

A DSS or batch processing system is probably the best application to take advantage of the Parallel Query option. The design of the Parallel Query option is centered around large queries that access large amounts of data, as is typical of both DSS and batch processing systems. Here is a short list of some of the operations that can be parallelized:

  Sorts
  Joins
  Table scans

Depending on your application, you may use one or more of these transaction types. Because these queries are typically long running and involve large amounts of disk I/O, you can drastically cut the time it takes to complete these queries with the use of the Parallel Query option.

Once you set up a test database and develop some test queries (hopefully similar to, if not exactly like production queries), run these queries with various degrees of parallelism to determine optimal performance.

Because batch jobs are often very repeatable, any tuning changes that are beneficial in your test environment are often just as beneficial in production.

The Parallel Query option can take advantage of the concurrent nature of disk arrays. By splitting up the queries into many small pieces, you may be able to request data from all the drives in your system simultaneously, optimizing I/O performance.

Oracle Parallel Server Option

The Oracle Parallel Server option may be very beneficial to your batch processing system in two ways:

1.  Performance. If your system is a candidate for the Parallel Server option, you should see significant performance improvements.
2.  Fault tolerance. By using the Oracle Parallel Server option, you can keep the system running, even if a computer fails. Keep in mind that batch processing systems do not typically demand the same uptime requirements as OLTP systems.

The Oracle Parallel Server option can enhance the performance of your batch processing system—but only if queries can be divided among the different systems in the cluster. In general, I do not think that the Oracle Parallel Server option is particularly useful in a batch processing system. Parallel server clusters are better suited for OLTP or decision support systems.


NOTE:  The Oracle Parallel Server option is not available on all platforms. Consult your system provider or Oracle to determine which platforms support the Oracle Parallel Server option.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.